#!/usr/bin/python
import sys , datetime , re
from sqlalchemy import *

SECONDS_OFFSET 		= 15 * 60	#	default 15m offset
SECONDS_RANGE  		= 5 * 60	# 		5m range
MAX_RETURN_ROWS 	= 10000		#	if exceeded no return

class DB():
	#	rdbms 		= ['postgresql','mysql','sqlite'...]  see sqlalchemy Doc
	#	time_field 	= the name of the timestamp field
	#	seconds_offset 	= the offset from machine current time
	#	seconds_range	= how large will be the 2 comparison groups.
	#	master_offset	= if set the 2 group will not be consecutive rather with master_offset seconds different between them
	#
	mandatory 	= ['host','db','user','password','rdbms','time_field','table']
	optional  	= ['port','schema','seconds_offset' , 'seconds_range' , 'master_offset' , "times_pattern" ]
	db 		= None
	db_conf		= None
	ranges		= []		#	in seconds
	times_ref	= []		#	holds the db server time references

	def __init__(self , db_conf , logger):
		self.logger = logger
		self.db_conf = db_conf


	def connect(self):
                if not self.validate():
                        self.logger("groups.log" , "Missing params. mandatory params are %s" % (" , ".join(self.mandatory)))
                        return false

		conn_str = "%s://%s:%s@%s/%s" % (self.db_conf['rdbms'] , self.db_conf['user'] , self.db_conf['password'] , self.db_conf['host'] , self.db_conf['db'])
		if self.db_conf['port'] : conn_str += "?port=%s" % (self.db_conf['port'])
		try:
			self.db = create_engine(conn_str)
		except Exception , e :
			self.logger._log("groups.log" , "Cant connect to %s.\n***  Server says : %s" % (conn_str , str(e)))
			return False
		return True


	def validate(self ):
		for f in self.mandatory:
			if f not in self.db_conf : 
				self.logger._log("groups.log" , "DB Missing params. \n***  mandatory are : %s \n***  givven : %s" % (self.mandatory , self.db_conf.keys()))
				return False
		if "fix_times" in self.db_conf :
			if len(self.db_conf["fix_times"]) != 4 :
				self.logger._log("groups.log" , "DB times_pattern Must be list of 4 and only 4")
				return False
		####	Add timestamp if fix_times comes with date \d{4}-\d{2}-\d{2} format
			for i , t in enumerate(self.db_conf["fix_times"]):
				if re.match(r'^\d{4}-\d{2}-\d{2}$',t) and i % 2 == 0	: self.db_conf["fix_times"][i] += " 00:00:00"
				elif re.match(r'^\d{4}-\d{2}-\d{2}$',t) and i % 2 == 1	: self.db_conf["fix_times"][i] += " 23:59:59"	


		self.db_conf['port'] 		= None 	 	 if not "port" in self.db_conf or self.db_conf['port'] == "3306" else self.db_conf['port'] 
		self.db_conf['schema'] 		= None 		 if not "schema" in self.db_conf else self.db_conf['schema']
		self.db_conf['seconds_offset'] 	= SECONDS_OFFSET if not "seconds_offset" in self.db_conf else int(self.db_conf['seconds_offset'])
		self.db_conf['seconds_range'] 	= SECONDS_RANGE  if not "seconds_range" in self.db_conf else int(self.db_conf['seconds_range'])
		self.db_conf['master_offset'] 	= 0		 if not "master_offset" in self.db_conf else int(self.db_conf['master_offset'])
		self.db_conf['max_rows']	= MAX_RETURN_ROWS if not "max_rows" in self.db_conf else int(self.db_conf['max_rows'])
		return True

	def select_rows(self,query):
		try:
			e = self.db.execute(query)
			if e.rowcount > self.db_conf['max_rows']:	# Max row exceeded dont return anything
				self.logger._log("groups.log" , "query exceeded max_rows %s returning %s rows for query :\n%s" % (self.db_conf['max_rows'] ,  e.rowcount , query))
				return None
			return e.fetchall()
		except Exception , e:
			self.logger._log("groups.log" , "Sql Exception on query : \n%s\n***%s" % (query , str(e)))


	def exists_table(self,table):
		str = "select count(*) t from pg_table_def where tablename = '%s'" % (table) if self.db_conf['rdbms'] == 'postgresql' \
			else "select count(*) t from information_schema.tables where table_name = '%s' and table_schema = '%s'" % (table , self.db_conf['db'])
		e = self.db.execute(str)
		res =  True if int(e.fetchone()[0]) > 0 else False
		return res


	def mk_ranges(self):
		self.ranges = [
			self.db_conf['seconds_offset'] + (2 * self.db_conf['seconds_range']) + self.db_conf['master_offset'],	#	master start
			self.db_conf['seconds_offset'] + self.db_conf['seconds_range'] + self.db_conf['master_offset'],   	#       master end
			self.db_conf['seconds_offset'] + self.db_conf['seconds_range'],						# 	slave start
			self.db_conf['seconds_offset'] ]



	#	get the current time from the rdbms
	def time_ref(self):
		self.times_ref = []
		self.mk_ranges()
		if self.db_conf['rdbms'] == 'postgresql' :
			str = """select getdate() + interval '-%s second' master_start , getdate() + interval '-%s second' master_end , getdate() + interval '-%s second' slave_start , getdate() + interval '-%s second' slave_end""" % (self.ranges[0],self.ranges[1],self.ranges[2],self.ranges[3])
		else :
			str = """select date_add(now() , interval -%s second) master_start , date_add(now() , interval -%s second) master_end , date_add(now() , interval -%s second) slave_start , date_add(now() , interval -%s second) slave_end""" % (self.ranges[0],self.ranges[1],self.ranges[2],self.ranges[3])
		try:
			e = self.db.execute(str)
			list = e.fetchone()
			for l in list:
				self.times_ref.append(l.strftime('%Y-%m-%d %H:%M:%S'))
		except:
			self.logger._log("groups.log" , "Some Connection problem")
			return False
		if "fix_times" in self.db_conf:  self.times_ref = self.db_conf["fix_times"]
		return True


#######################################################

